'Imports System.Data.OleDb
Imports System.Data.SqlClient


Public Class ServerConnection
    Public Function ServerConnecting() As Boolean
        Try
            MyServerCn = New SqlConnection
            'MyServerCn.ConnectionString = "Data Source=vsip-server02x\sqlserver2008;Initial Catalog=Warehouse;Integrated Security=True;Trusted_Connection=True"
            'MyServerCn.ConnectionString = "Data Source=10.0.0.2\sqlserver2008;Initial Catalog=Warehouse;Integrated Security=True;Trusted_Connection=True"
            MyServerCn.ConnectionString = "Data Source=10.0.0.2\sqlserver2008;Initial Catalog=Warehouse;persist security info=False;user id=sa;pwd=ttfi_1985"
            'MyServerCn.ConnectionString = "Data Source=IT-DQHOAN\SQL2008;initial catalog=Warehouse;persist security info=False;user id=sa;pwd=123456"
            ' MyServerCn.ConnectionString = "Data Source=HQHUNG;Initial Catalog=Warehouse;Integrated Security=True;Trusted_Connection='YES'"
            ' MyServerCn.ConnectionString = "Data Source=HQHUNG\LOCAL;Initial Catalog=Ex_Warehouse;Integrated Security=True"
            MyServerCn.Open()
            Return True
        Catch ex As Exception
            'ErrRecording(MyLine, "DataConnection/ServerConnecting", ex.Message)
            Return False
        End Try
    End Function

    Public Function ExecuteProcedureSQLSVR(ByVal _spName As String) As DataSet
        Dim ds As New DataSet
        'Dim cmd As New OleDbCommand
        Dim cmd As New SqlCommand
        'Dim da As New OleDb.OleDbDataAdapter
        Dim da As New SqlDataAdapter

        Try
            If MyServerCn.State = ConnectionState.Closed Then
                MyOnlineFlg = False
                ErrRecording("", "DBConnection/ExecuteProcedureSQLSVR", "Can not connect to SQL Server ")
                If MyServerConnection.ServerConnecting() = False Then Return ds
            End If
            cmd.Connection = MyServerCn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = _spName
            da.SelectCommand = cmd
            da.Fill(ds)
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            ErrRecording("", "DBConnection/ExecuteProcedureSQLSVR", ex.Message)
        Finally
            cmd.Dispose()
            da.Dispose()
        End Try
        Return ds
    End Function

    Public Function ExecuteProcedureSQLSVR_1(ByVal _spName As String) As DataTable
        Dim cmd As New SqlCommand
        Dim MyReader As SqlDataReader = Nothing
        Dim MyTable As New DataTable
        Try
            If MyServerCn.State = ConnectionState.Closed Then
                MyOnlineFlg = False
                ErrRecording("", "DBConnection/ExecuteProcedureSQLSVR", "Can not connect to SQL Server ")
                If MyServerConnection.ServerConnecting() = False Then Return MyTable
            End If
            cmd.Connection = MyServerCn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = _spName
            MyReader = cmd.ExecuteReader()
            If MyReader.HasRows Then
                For MyIndex As Integer = 0 To MyReader.FieldCount - 1
                    MyTable.Columns.Add(New DataColumn(MyReader.GetName(MyIndex), MyReader.GetFieldType(MyIndex)))
                Next
                Dim MyValues(MyReader.FieldCount - 1) As Object
                Dim MyRowIndex As Integer = 0
                While MyReader.Read()
                    MyReader.GetValues(MyValues)
                    MyTable.Rows.Add(MyValues)
                End While
                MyRowIndex += 1
            End If
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            ErrRecording("", "DBConnection/ExecuteProcedureSQLSVR_1", ex.Message)
        Finally
            cmd.Dispose()
            MyReader.Close()
            MyReader = Nothing
        End Try
        Return MyTable
    End Function

    Public Function ExecuteSQLSVR(ByVal _spName As String) As Boolean
        Dim result As Boolean = False
        'Dim cmd As New OleDbCommand
        Dim cmd As New SqlClient.SqlCommand
        Try
            If MyServerCn.State = ConnectionState.Closed Then
                MyOnlineFlg = False
                ErrRecording("", "DBConnection/ExecuteSQLSVR", "Can not connect to SQL Server ")
                If MyServerConnection.ServerConnecting() = False Then Return result
            End If

            cmd.Connection = MyServerCn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = _spName
            If cmd.ExecuteNonQuery <> -1 Then
                result = True
            End If
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            ErrRecording("", "DBConnection/ExecuteSQLSVR", ex.Message)
        Finally
            cmd.Dispose()
        End Try
        Return result
    End Function

    Public Function GetFormTime(ByVal ID As String) As String
        Dim result As String = ""
        Dim ds As DataSet
        ds = ExecuteProcedureSQLSVR("SELECT [Outputtime] FROM [Manufacturing] WHERE [ID] = '" & ID & "'")
        If ds.Tables(0).Rows.Count <> 0 Then
            result = ds.Tables(0).Rows(0)(0)
        End If
        Return result
    End Function

    Public Sub Disposing()
        If Not MyServerCn Is Nothing Then
            If MyServerCn.State = ConnectionState.Open Then
                MyServerCn.Close()
            End If
            MyServerCn.Dispose()
        End If
    End Sub
End Class
